package services;

import java.io.File;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.Workbook;

import utils.POIUtils;

import dao.Filter;
import dao.ObjectDao;
import db.Conexion;
import exceptions.ExDBException;

import business.CompanyPoll;
import business.Country;
import business.CoverDefinition;
import business.FormMovement;
import business.Indicator;
import business.IndustrialMP;
import business.IndustrialProduct;
import business.Local;
import business.LocalMenu;
import business.MiningItem;
import business.Poll;
import business.PollStatus;
import business.PollValue;

public class PollService {
	
	public final static Long POLL_E_ID = 1L;
	public final static Long POLL_L_ID = 2L;
	public final static Long POLL_LR_ID = 34L;
	public final static Long POLL_ACPI_ID = 20L;
	public final static Long POLL_ME_ID = 29L;
	public final static Long POLL_MPB_ID = 30L;
	public final static Long POLL_PB_ID = 31L;
	public final static Long POLL_EPN_ID = 32L;
	public final static Long POLL_EI_ID = 35L;
	public final static Long POLL_EIC_ID = 36L;
	public final static Long POLL_EIMC_ID = 37L;
	
	public final static String COMPANY_TABLE = "EMPRESA";
	public final static String LOCAL_TABLE = "LOCAL";
	public final static String CAMPANA_VALUES = "CAMPANA";
	public final static String LIST_VALUES = "LIST";
	
	public final static String LIST_MINNING = "mineria";
	public final static String LIST_COUNTRY = "pais";	
	
	public final static String ALL_FORMS_SUFIX = "ALL";
	
	public final static Long POLL_STATUS_NEW = 1L;
	public final static Long POLL_STATUS_EDITED = 2L;
	public final static Long POLL_STATUS_PENDING_REVIEW = 3L;
	public final static Long POLL_STATUS_REVIEW = 4L;
	public final static Long POLL_STATUS_REJECTED = 5L;
	public final static Long POLL_STATUS_APPROVED = 6L;
	
	public final static Long FORM_STATUS_NEW = 1L;
	public final static Long FORM_STATUS_EDITED = 2L;
	public final static Long FORM_STATUS_FINISHED = 3L;
	public final static Long FORM_STATUS_VALIDATED_OK = 4L;
	public final static Long FORM_STATUS_VALIDATED_ERROR = 5L;
	public final static Long FORM_STATUS_PENDING_REVIEW = 6L;
	public final static Long FORM_STATUS_APPROVED = 7L;
	public final static Long FORM_STATUS_REJECTED = 8L;
	
	public final static Long INDICATORS_WITH_TRANSFERENCE = 1L;
	public final static Long INDICATORS_REGIONALIZED = 2L;

	public List<PollValue> getPollValues(Long pollID,Long companyCampaignID,Long localID)
	{
		ObjectDao dao= new ObjectDao();
		return dao.getPollValues(companyCampaignID, pollID, localID);
	}
	
	public List<CoverDefinition> getCoverDefinitionsByPoll(Long pollID)
	{				
		ObjectDao dao= new ObjectDao();
		return dao.getCoverDefinitions(pollID);
	}
	
	public List<CompanyPoll> getPollsByCompany(String cuit)
	{				
		ObjectDao dao= new ObjectDao();
		List<Filter> filters = new ArrayList<Filter>();
		Filter filter = new Filter(Filter.STRING_FILTER, "ENC_EMP.CUIT_EMPRESA", Filter.EQUAL_OPERATOR,cuit,true);
		filters.add(filter);
		return dao.getCompanyPolls(filters, ObjectDao.AND_OPERATOR);
	}
	
	public Poll getPollByName(String pollName)
	{
		ObjectDao dao= new ObjectDao();
		List<Filter> filters = new ArrayList<Filter>();
		Filter filter = new Filter(Filter.STRING_FILTER, "REPLACE(ENC.NOMBRE_ENCUESTA,'\"','')", Filter.LIKE_OPERATOR,pollName,true);
		filters.add(filter);
		List<Poll> polls = dao.getPolls(filters, ObjectDao.AND_OPERATOR);
		if(polls != null && polls.size() == 1)
		{
			return polls.get(0);
		}
		else
		{
			return null;
		}
	}
	
	public Poll getPollByName(String pollName,Conexion conn)
	{
		ObjectDao dao= new ObjectDao();
		List<Filter> filters = new ArrayList<Filter>();
		Filter filter = new Filter(Filter.STRING_FILTER, "REPLACE(ENC.NOMBRE_ENCUESTA,'\"','')", Filter.LIKE_OPERATOR,pollName,true);
		filters.add(filter);
		List<Poll> polls = dao.getPolls(filters, ObjectDao.AND_OPERATOR,conn);
		if(polls != null && polls.size() == 1)
		{
			return polls.get(0);
		}
		else
		{
			return null;
		}
	}
	
	public List<CompanyPoll> getPollsByCompanyAndLocal(Long campaignID, Long localID)
	{				
		ObjectDao dao= new ObjectDao();
		List<Filter> filters = new ArrayList<Filter>();
		Filter filter = new Filter(Filter.NUMERIC_FILTER, "ENC_EMP.ID_EMP_CAMP", Filter.EQUAL_OPERATOR,campaignID,true);
		filters.add(filter);
		filter = new Filter(Filter.NUMERIC_FILTER, "ENC_EMP.ID_LOCAL", Filter.EQUAL_OPERATOR,localID,true);
		filters.add(filter);
		return dao.getCompanyPolls(filters, ObjectDao.AND_OPERATOR);
	}

	/**
	 * 
	 * @param cuit
	 * @return Devuelve la encuesta de la empresa (Formulario E)
	 */
	public CompanyPoll getCompanyPoll(String cuit)
	{
		ObjectDao dao= new ObjectDao();
		List<Filter> filters = new ArrayList<Filter>();
		Filter filter = new Filter(Filter.STRING_FILTER, "ENC_EMP.CUIT_EMPRESA", Filter.EQUAL_OPERATOR,cuit,true);
		filters.add(filter);
		filter = new Filter(Filter.NUMERIC_FILTER, "ENC_EMP.ID_ENCUESTA", Filter.EQUAL_OPERATOR,POLL_E_ID,true);
		filters.add(filter);
		List<CompanyPoll> encuestasEmpresaList = dao.getCompanyPolls(filters, ObjectDao.AND_OPERATOR);
		if(encuestasEmpresaList != null && encuestasEmpresaList.size() == 1)
		{
			return encuestasEmpresaList.get(0);
		}
		else
		{
			return null;
		}
	}
			
	public List<CompanyPoll> getCompanyPolls(Long campaignID, Long[] pollIDs)
	{
		ObjectDao dao= new ObjectDao();
		List<Filter> filters = new ArrayList<Filter>();
		String pollIDsStr = "";
		for(Long long1 : pollIDs)
		{
			pollIDsStr += long1 + ",";
		}
		if(pollIDsStr != null && pollIDsStr.trim().length() > 0)
		{
			pollIDsStr = pollIDsStr.substring(0, pollIDsStr.length() - 1);
		}
		Filter filter = new Filter(" WHERE ENC_EMP.ID_EMP_CAMP = " + campaignID + " AND ENC_EMP.ID_ENCUESTA IN (" + pollIDsStr + ") order by ENC_EMP.ID_ENCUESTA");
		filters.add(filter);
		List<CompanyPoll> encuestasEmpresaList = dao.getCompanyPolls(filters, ObjectDao.AND_OPERATOR);
		if(encuestasEmpresaList != null && encuestasEmpresaList.size() > 0)
		{
			return encuestasEmpresaList;
		}
		else
		{
			return null;
		}
	}
	
	public CompanyPoll getLocalPoll(Long localID, Long companyCampaignID)
	{
		ObjectDao dao= new ObjectDao();
		List<Filter> filters = new ArrayList<Filter>();
		Filter filter = new Filter(Filter.NUMERIC_FILTER, "ENC_EMP.ID_LOCAL", Filter.EQUAL_OPERATOR,localID,true);
		filters.add(filter);
		filter = new Filter(Filter.NUMERIC_FILTER, "ENC_EMP.ID_ENCUESTA", Filter.EQUAL_OPERATOR,POLL_L_ID,true);
		filters.add(filter);
		filter = new Filter(Filter.NUMERIC_FILTER, "ENC_EMP.ID_EMP_CAMP", Filter.EQUAL_OPERATOR,companyCampaignID,true);
		filters.add(filter);
		List<CompanyPoll> encuestasEmpresaList = dao.getCompanyPolls(filters, ObjectDao.AND_OPERATOR);
		if(encuestasEmpresaList != null && encuestasEmpresaList.size() == 1)
		{
			return encuestasEmpresaList.get(0);
		}
		else
		{
			return null;
		}
	}
		
	public void regionalize(Long companyCampaignID) throws SQLException
	{
		ObjectDao dao= new ObjectDao();
		dao.regionalize(companyCampaignID);
	}
	
	public List<Indicator> getIndicators(Long companyCampaignID, Long localID, Long type)
	{
		ObjectDao dao= new ObjectDao();
		return dao.getIndicators(companyCampaignID, localID, type);
	}
	
	public CompanyPoll getRegionalizedLocalPoll(Long localID, Long companyCampaignID)
	{
		ObjectDao dao= new ObjectDao();
		List<Filter> filters = new ArrayList<Filter>();
		Filter filter = new Filter(Filter.NUMERIC_FILTER, "ENC_EMP.ID_LOCAL", Filter.EQUAL_OPERATOR,localID,true);
		filters.add(filter);
		filter = new Filter(Filter.NUMERIC_FILTER, "ENC_EMP.ID_ENCUESTA", Filter.EQUAL_OPERATOR,POLL_LR_ID,true);
		filters.add(filter);
		filter = new Filter(Filter.NUMERIC_FILTER, "ENC_EMP.ID_EMP_CAMP", Filter.EQUAL_OPERATOR,companyCampaignID,true);
		filters.add(filter);
		List<CompanyPoll> encuestasEmpresaList = dao.getCompanyPolls(filters, ObjectDao.AND_OPERATOR);
		if(encuestasEmpresaList != null && encuestasEmpresaList.size() == 1)
		{
			return encuestasEmpresaList.get(0);
		}
		else
		{
			return null;
		}
	}
	
	public List<FormMovement> getFormMovementsNotApprovedByCompanyCampaign(Long companyCampaignID)
	{
		ObjectDao dao= new ObjectDao();
		List<Filter> filters = new ArrayList<Filter>();
		Filter filter = new Filter(Filter.NUMERIC_FILTER, "EE.ID_EMP_CAMP", Filter.EQUAL_OPERATOR,companyCampaignID,true);
		filters.add(filter);
		filter = new Filter("NOT MF.ID_ESTADO_FORMULARIO = " + PollService.FORM_STATUS_APPROVED);
//		filter = new Filter(Filter.NUMERIC_FILTER, "MF.ID_ESTADO_FORMULARIO", Filter.EQUAL_OPERATOR,PollService.FORM_STATUS_APPROVED,true);
		filters.add(filter);
		List<FormMovement> formMovementList = dao.getFormMovements(filters, ObjectDao.AND_OPERATOR);
		return formMovementList;
	}
	
	public PollStatus getPollStatusByID(Long pollStatusID)
	{
		ObjectDao dao= new ObjectDao();
		List<Filter> filters = new ArrayList<Filter>();
		Filter filter = new Filter(Filter.NUMERIC_FILTER, "EST_ENC.ID_EST_ENC", Filter.EQUAL_OPERATOR,pollStatusID,true);
		filters.add(filter);
		
		List<PollStatus> pollStatuses = dao.getPollStatuses(filters, ObjectDao.AND_OPERATOR);

		if(pollStatuses != null && pollStatuses.size() == 1)
		{
			return pollStatuses.get(0);
		}
		return null;
	}
	
	public void savePollValues(List<PollValue> pollValues,Long userID,Long formStatus) throws ExDBException
	{
		ObjectDao dao= new ObjectDao();
		dao.savePollValues(pollValues,userID,formStatus);
	}
	
	public List<Country> getCountries()
	{
		ObjectDao dao= new ObjectDao();
		return dao.getCountries(null,null);
	}
	public List<MiningItem> getMiningItems()
	{
		ObjectDao dao= new ObjectDao();
		return dao.getMiningItems(null,null);
	}
//__________________________________________________________________________________________________________
	public List<IndustrialProduct> getIndustrialProducts(String valor)
	{
		ObjectDao dao= new ObjectDao();
		List<Filter> filters = new ArrayList<Filter>();
		Filter filter = new Filter(Filter.STRING_FILTER, "CP.CODCLAPR", Filter.EQUAL_OPERATOR,valor,true);
		filters.add(filter);
		
		return dao.getIndustrialProducts(filters, ObjectDao.AND_OPERATOR);
	}
//_____________________________________________________________________________________________________
/* Incorporar lista para seleccionar Materias Primas 
	Copio el formato y estructura de busqueda y filtrado de lo echo para productos
	*/
	public List<IndustrialMP> getIndustrialMP(String valor)
	{
		ObjectDao dao= new ObjectDao();
		List<Filter> filters = new ArrayList<Filter>();
		Filter filter = new Filter(Filter.STRING_FILTER, "CP.CODCLA", Filter.EQUAL_OPERATOR,valor,true);
		filters.add(filter);
		
		return dao.getIndustrialMP(filters, ObjectDao.AND_OPERATOR);
	}
	
//____________________________________________________________________________________________________
	public void updateFormStatusByCompanyCampaign(Long companyCampaignID,Long statusID) throws ExDBException
	{
		ObjectDao dao = new ObjectDao();
		dao.updateFormStatusByCompanyCampaign(companyCampaignID, statusID);
	}

	public void updateFormStatus(Long pollCompanyID,Long status,Long userID) throws ExDBException
	{
		ObjectDao dao = new ObjectDao();
		dao.updateFormStatus(pollCompanyID, status, userID, null);
	}
	
	public void updateFormStatus(Long pollCompanyID,Long status,Long userID,String comments) throws ExDBException
	{
		ObjectDao dao = new ObjectDao();
		dao.updateFormStatus(pollCompanyID, status, userID, comments);
	}
		
	public void updatePollStatus(Long pollCompanyID,Long status) throws ExDBException
	{
		ObjectDao dao = new ObjectDao();
		dao.updatePollStatus(pollCompanyID, status);
	}

	public void updatePollAndFormStatus(Long pollCompanyID,Long pollStatusID,Long formStatusID) throws ExDBException
	{
		ObjectDao dao = new ObjectDao();
		dao.updatePollAndFormStatus(pollCompanyID, pollStatusID, formStatusID);
	}
	
	public void execSum(Long companyCampaignID) throws ExDBException
	{
		ObjectDao dao= new ObjectDao();
		dao.execSUM(companyCampaignID);
	}
	
	public void execSumVariables(Long companyCampaignID) throws ExDBException
	{
		ObjectDao dao= new ObjectDao();
		dao.execSUMVariables(companyCampaignID);
	}
	
	public String getVariableFormE(Long companyCampaignID, String variable)
	{
		StringBuilder sqlBld = new StringBuilder();
		sqlBld.append("select VALOR from ENCUESTAS_EMPRESAS_VALORES where ID_ENC_DEF = " +
				"(SELECT ID_ENC_DEF FROM ENCUESTAS_DEFINICION WHERE DEFINICION LIKE '");
		sqlBld.append(variable);
		sqlBld.append("') and ID_ENC_EMP IN (SELECT ID_ENC_EMP FROM ENCUESTAS_EMPRESAS WHERE ID_EMP_CAMP = ");
		sqlBld.append(companyCampaignID);
		sqlBld.append(")");
		
		ObjectDao dao= new ObjectDao();
		return dao.getFirstString(sqlBld.toString());
	}
	
	public List<String> getVariablesFormL(Long companyCampaignID, String variable)
	{
		StringBuilder sqlBld = new StringBuilder();
		sqlBld.append("select valor from ENCUESTAS_EMPRESAS_VALORES where ID_ENC_DEF = (SELECT ID_ENC_DEF FROM ENCUESTAS_DEFINICION WHERE DEFINICION LIKE '");
		sqlBld.append(variable);
		sqlBld.append("') and ID_ENC_EMP IN (SELECT ID_ENC_EMP FROM ENCUESTAS_EMPRESAS WHERE ID_EMP_CAMP = ");
		sqlBld.append(companyCampaignID);
		sqlBld.append(")");		
		
		ObjectDao dao= new ObjectDao();
		return dao.getStringList(sqlBld.toString());
	}
	
	public String getVariableFormL(Long companyCampaignID, String variable, Long localID)
	{
		StringBuilder sqlBld = new StringBuilder();
		
		sqlBld.append("select valor from ENCUESTAS_EMPRESAS_VALORES where ID_ENC_DEF = (SELECT ID_ENC_DEF FROM ENCUESTAS_DEFINICION WHERE DEFINICION LIKE '");
		sqlBld.append(variable);
		sqlBld.append("') and ID_ENC_EMP IN (SELECT ID_ENC_EMP FROM ENCUESTAS_EMPRESAS WHERE ID_EMP_CAMP = ");
		sqlBld.append(companyCampaignID);
		sqlBld.append(" and ID_LOCAL = ");
		sqlBld.append(localID);		
		sqlBld.append(")");
				
		ObjectDao dao= new ObjectDao();
		return dao.getFirstString(sqlBld.toString());
	}
	
	public List<LocalMenu> getMenuLocalsByCompany(Long companyCampaignID)
	{
		List<LocalMenu> localesDeEmpresa = new ArrayList<LocalMenu>();
		
		ObjectDao dao= new ObjectDao();
		List<Filter> filters = new ArrayList<Filter>();
		Filter filter = new Filter(" WHERE LOC.ID_LOCAL in (select distinct (ENC_EMP2.id_local) from ENCUESTAS_EMPRESAS ENC_EMP2 where ENC_EMP2.ID_EMP_CAMP = " + companyCampaignID + " and ENC_EMP2.ID_LOCAL IS NOT NULL)");
		filters.add(filter);
		List<Local> locales = dao.getLocals(filters, ObjectDao.AND_OPERATOR);
		LocalMenu localMenu;
		
		for(Local local : locales)
		{
			Long idLocal = Long.valueOf(local.getId());
			localMenu = new LocalMenu();
			localMenu.setLocal(local);
			localMenu.setLocalPoll(this.getLocalPoll(idLocal,companyCampaignID));
			localMenu.setRegionalizedLocalPoll(this.getRegionalizedLocalPoll(idLocal,companyCampaignID));
			localMenu.setAnnexes(this.getPollsByCompanyAndLocal(companyCampaignID,idLocal));
			
			localesDeEmpresa.add(localMenu);
		}		
		return localesDeEmpresa;		
	}
	
	public List<LocalMenu> getMenuLocalsByCompanyFilterByType(Long companyCampaignID,String typeFilter)
	{
		List<LocalMenu> localesDeEmpresa = new ArrayList<LocalMenu>();
		
		ObjectDao dao= new ObjectDao();
		List<Filter> filters = new ArrayList<Filter>();
		Filter filter = new Filter(" WHERE LOC.ID_LOCAL in (select distinct (ENC_EMP2.id_local) from ENCUESTAS_EMPRESAS ENC_EMP2 where ENC_EMP2.ID_EMP_CAMP = " + companyCampaignID + " and ENC_EMP2.ID_LOCAL IS NOT NULL) AND LOC.ID_TIPO_LOCAL IN " + typeFilter);
		filters.add(filter);
		List<Local> locales = dao.getLocals(filters, ObjectDao.AND_OPERATOR);
		LocalMenu localMenu;
		
		for(Local local : locales)
		{
			Long idLocal = Long.valueOf(local.getId());
			localMenu = new LocalMenu();
			localMenu.setLocal(local);
			localMenu.setLocalPoll(this.getLocalPoll(idLocal,companyCampaignID));
			localMenu.setRegionalizedLocalPoll(this.getRegionalizedLocalPoll(idLocal,companyCampaignID));
			localMenu.setAnnexes(this.getPollsByCompanyAndLocal(companyCampaignID,idLocal));
			
			localesDeEmpresa.add(localMenu);
		}		
		return localesDeEmpresa;		
	}
	
	public void saveRejectCommentByID(Long formMovementID,Long userID, String observation) throws ExDBException
	{
		ObjectDao dao = new ObjectDao();
		dao.updateRejectComment(formMovementID,userID,observation);
	}
	
	public boolean checkIsStartRevision(Long companyCampaign)
	{
		ObjectDao dao = new ObjectDao();
		return dao.checkIsStartRevision(companyCampaign);
	}
	
	public List<String> getLockedFieldsFormE()
	{
		ObjectDao dao = new ObjectDao();
		return dao.getLockedFieldsFormE();
	}
		
	public void processForm(File fileSrc,Long companyCampaignID,String pollName,Long localID,Conexion conn,Statement stmt,Statement stmt2) throws ExDBException, SQLException
	{
		Poll poll = this.getPollByName(pollName.replace(".xls", ""),conn);	
		if(poll != null)
		{
			Workbook wb = POIUtils.getWorkbook(fileSrc);
			ObjectDao dao = new ObjectDao();
			dao.processForm(wb, companyCampaignID, poll.getId(),localID, stmt, stmt2);
		}
	}
	
	private void readFiles(File dirFile,Long companyCampaignID,Conexion conn, Statement stmt, Statement stmt2) throws ExDBException, SQLException
	{
		String localName = dirFile.getName();
		Long localID = null;
		if(localName.trim().startsWith("Local "))
		{
			localID = Long.valueOf(localName.substring(6,localName.length()));
		}
		
		File[] unzipFiles = dirFile.listFiles();
		for(File currentUnzipFile : unzipFiles)
		{
			if(currentUnzipFile.isDirectory())
			{
				readFiles(currentUnzipFile,companyCampaignID,conn,stmt,stmt2);
			}
			else
			{
				if(localID == null)
				{
					localID = Long.valueOf(0);
				}
				String pollName = currentUnzipFile.getName();
				processForm(currentUnzipFile,companyCampaignID,pollName,localID,conn,stmt,stmt2);
			}
		}
	}
	
	public void processForms(File dirFile,Long companyCampaignID)
	{
		Conexion conn = new Conexion();
		Statement stmt = null;
		Statement stmt2 = null;
		try {
						
			conn.abrirConexion();
			stmt = conn.createStatement();
			stmt2 = conn.createStatement();
			
			readFiles(dirFile,companyCampaignID,conn,stmt,stmt2);
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		finally{
			conn.closeStatement(stmt);
			conn.closeStatement(stmt2);
			conn.cerrarConexion();
		}
		try {
			this.execSum(companyCampaignID);
			//TODO MENSAJE QUE SALIO TODOO BIEN
		} catch (Exception e) {
			//TODO MENSAJE QUE SALIO TODOO MAL
			e.printStackTrace();
		}
	}
	
}